package com.ly.mpayment.core.util;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.core.text.csv.CsvWriter;
import cn.hutool.core.util.CharsetUtil;
import com.ly.mpayment.persist.entity.*;
import com.ly.mpayment.persist.entity.slow.SlowOrder;
import com.ly.mpayment.persist.service.bill.BillOrderService;
import com.ly.mpayment.persist.service.slow.SlowOrderService;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import com.ly.mpayment.persist.hibernate.Page;
import com.ly.mpayment.persist.service.downrechargeflow.DownRechargeFlowServiceImpl;
import com.ly.mpayment.persist.service.downrechargeflow.IDownRechargeFlowService;
import com.ly.mpayment.persist.service.uporder.IUporderService;
import com.ly.mpayment.persist.service.uporder.UporderServiceImpl;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.springframework.core.io.ClassPathResource;

/**
 * 描述：使用POI创建导出报表Excel
 * @ClassName: ExportExcelUtil
 * @Description: TODO
 * @date 2013-8-7 下午2:35:27
 *
 */
public class ExportExcelUtil {

	/**
	 * 下游产品导出
	 * @return
	 */
	 public static String exportDownProduct(List<DownShopProduct> products,String name){
		    String  inputFileName = CookieRedis.get("exportPath");
		    SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		    String now = dateFormat.format(new Date());
		    //导出文件路径
		    String basePath = inputFileName;
		    //文件名
		    String exportFileName = name+"_"+now+".xlsx";

		    String[] cellTitle = {"编号","运营商","省份","地市","产品编码","面值","标准价","折后价","折扣"};
		    // 声明一个工作薄
		    XSSFWorkbook workBook = null;
		    workBook = new XSSFWorkbook();
		    // 生成一个表格
		    XSSFSheet sheet = workBook.createSheet();
		    workBook.setSheetName(0,"下游产品");
		    // 创建表格标题行 第一行
		    XSSFRow titleRow = sheet.createRow(0);
		    for(int i=0;i<cellTitle.length;i++){
		        titleRow.createCell(i).setCellValue(cellTitle[i]);
		    }
		    //插入需导出的数据
		    for(int i= 0;i < products.size();i++){
		        XSSFRow row = sheet.createRow(i+1);
		        row.createCell(0).setCellValue(i+1);
		        DownShopProduct product = products.get(i);
				row.createCell(1).setCellValue(product.getOperators().getOperatorname());
				row.createCell(2).setCellValue(product.getProvincialInformation().getProvincename());
				row.createCell(3).setCellValue(product.getCityName() == null ? "" : product.getCityName());

		        row.createCell(4).setCellValue(product.getDownprouuid());
		        row.createCell(5).setCellValue(product.getBusinessType().getBusname().equals(Config.faceValue[0])
						? product.getDownfacevalue().concat(Config.faceValue[0]) : product.getDownfacevalue().concat(Config.faceValue[1]));

		        row.createCell(6).setCellValue(product.getPrice().toString());
		        row.createCell(7).setCellValue(product.getDiscountAmount().toString());
		        row.createCell(8).setCellValue(product.getDiscount().toString());
		    }
		    File  file = new File(basePath+exportFileName);
		    //文件输出流
		    FileOutputStream outStream;
			try {
				outStream = new FileOutputStream(file);
				 workBook.write(outStream);
				    outStream.flush();
				    outStream.close();
				    System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
				    return basePath+exportFileName;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return null;
	 }

	/**
	 * 下游订单导出
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static String exportDownFlow(IDownRechargeFlowService flowService, Map<String, Object> parameter, BillOrderService billOrderService) throws IOException {

		DownRechargeFlowServiceImpl serviceImpl = (DownRechargeFlowServiceImpl) flowService;
		String inputFileName = CookieRedis.get("exportPath");
		SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		String now = dateFormat.format(new Date());
		//导出文件路径
		String basePath = inputFileName;
		//文件名
		String exportFileName = "order_"+now+".csv";
		BillOrder order = new BillOrder();
		order.setCreateTime(DateUtils.getNowDate());
		order.setPath(CookieRedis.get("download").concat(File.separator)+(basePath+exportFileName).replace(CookieRedis.get("exportPath"), ""));
		order.setType(1);
		order.setFileType(Config.order);
		billOrderService.saveBill(order);
		syncExprotDown(flowService,parameter,billOrderService,order,basePath +exportFileName ,serviceImpl);
		return basePath+exportFileName;
	}

	/**
	 * 下游异步导出
	 * @param flowService
	 * @param parameter
	 * @param billOrderService
	 * @param orderBill
	 * @param path
	 * @param serviceImpl
	 * @throws Exception
	 */
	private static void syncExprotDown(IDownRechargeFlowService flowService,final Map<String, Object> parameter,BillOrderService billOrderService,final BillOrder orderBill,
									   final String path,DownRechargeFlowServiceImpl serviceImpl) {
		new Thread(new Runnable() {
			@Override
			public void run() {
				// 声明一个工作薄
				try {
					CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader("编号","渠道名称","省份","业务类型","商家订单号","充值号码","实际金额","扣款金额","面值","折扣","状态","充值时间","完成时间","备注","平台分销订单号");
					FileOutputStream fos = new FileOutputStream(path);
					OutputStreamWriter osw = new OutputStreamWriter(fos, "GBK");

					CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat);

					int start = 0 ;
					int pageSize = Integer.valueOf(CookieRedis.get("exportPageSize"));
					Page page = new Page();
					Map<String, Object> mapResult = MapUtil.getMap();
					mapResult.putAll(parameter);
					long totalCount =  serviceImpl.findCountResult(getFlowOrderSqlDownCount(parameter) , MapUtil.getMap());

					page.setPageSize(totalCount > pageSize ? pageSize : (int)totalCount);

					int count = (int) (totalCount / pageSize) <= 0 ? 1 : (int) (totalCount / pageSize) + 1;
					DownRechargeFlow name = null;
					for(int j = 0 ; j < count ; j++){
						start = start+1;
						page.setStart(start);
						List<DownRechargeFlow> orders = PaginationUtil.queryPaginationNotCount(getFlowOrderSql(mapResult), page, serviceImpl.getSessionFactory().openSession()).getResult();
						//插入需导出的数据
						for(int i= 0;i < orders.size();i++){
							if(j == 0){
								name = orders.get(i);
							}
							DownRechargeFlow flow = orders.get(i);
							csvPrinter.printRecord((j * pageSize) + i  +1 , "", flow.getProvince(),flow.getDownShopProduct() == null ? "" :flow.getDownShopProduct().getBusinessType().getBusname()
									,flow.getDownreqid()+"\t",flow.getDowonphonenum(),flow.getRechargeamount() == null ? "" :  flow.getRechargeamount()+"",flow.getDiscountAmount() == null ? "" : flow.getDiscountAmount()+"",flow.getDownShopProduct() == null ? "" : ( flow.getDownShopProduct().getBusinessType().getBusname().equals(Config.telephonefare[0])|| flow.getDownShopProduct().getBusinessType().getBusname().equals("固话") )? flow.getDownShopProduct().getDownfacevalue().concat("话费") : flow.getDownShopProduct().getDownfacevalue().concat("M")
									,flow.getDiscount() == null ? "" : flow.getDiscount()+"",flow.getStatus() == 1 ? "充值中" : flow.getStatus() == 2 ? "充值成功" : flow.getStatus() == 3 ? "充值失败" : flow.getStatus() == 4 ? "成功后取消的订单" :
											flow.getStatus() == 5 ? "等待充值" :  flow.getStatus() == 6 ? "退款成功" :  flow.getStatus() == 7 ? "退款失败" :  flow.getStatus() == 8 ? "失败后取消的订单" :  "未知","\t"+DateUtils.toLongDate(flow.getCreatetime())+"\t", flow.getDownendtime() != null? "\t"+DateUtils.toLongDate(flow.getDownendtime())+"\t" : "",flow.getRemark(),flow.getDownorderuuid());
						}
					}
					csvPrinter.flush();
					csvPrinter.close();
					System.out.println("导出2007文件成功！文件导出路径：--"+path);
					orderBill.setShopId(name != null ? name.getUserFlow().getUseruuid() : "");
					orderBill.setName(name != null ? name.getUserFlow().getUsername() : "");
					billOrderService.updateBill(orderBill);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}).start();
	}


	 public static String getFlowOrderSql(Map<String, Object> parameters){
			String hql = "select f from DownRechargeFlow f left join fetch f.userFlow u  left join fetch f.downShopProduct dos left join fetch dos.businessType bu  where f.status != 0 ";

			if(parameters.containsKey(":operatoruid")){
				hql += " and f.operators ='".concat(parameters.get(":operatoruid").toString()).concat("' ");
			}
			if(parameters.containsKey(":useruuid")){
				hql += " and u.useruuid ='".concat(parameters.get(":useruuid").toString()).concat("' ");
			}
			if(parameters.containsKey(":busuuid")){
				hql += " and bu.busuuid ='".concat(parameters.get(":busuuid").toString()).concat("' ");
			}
			if(parameters.containsKey(":provinceuuid")){
				hql += " and f.province ='".concat(parameters.get(":provinceuuid").toString()).concat("' ");
			}
			if(parameters.containsKey(":downreqid")){
				hql += " and f.downreqid = '".concat(parameters.get(":downreqid").toString()).concat("' ");
			}
			if(parameters.containsKey(":phoneNum")){
				hql += " and f.dowonphonenum = '".concat(parameters.get(":phoneNum").toString().trim()).concat("' ");
			}
			if(parameters.containsKey(":downorderuuid")){
				hql += " and f.downorderuuid = '".concat(parameters.get(":downorderuuid").toString().trim()).concat("' ");
			}
			if(parameters.containsKey(":status")){
				hql += " and f.status = '".concat(parameters.get(":status").toString().trim()).concat("' ");
			}
			if(parameters.containsKey(":callstatus")){
				hql += " and f.callStatus = ".concat(parameters.get(":callstatus").toString().trim()).concat(" ");
			}
			if(parameters.containsKey(":downfacevalue")){
				hql += " and dos.downfacevalue = ".concat(parameters.get(":downfacevalue").toString().trim()).concat(" ");
			}

			if(parameters.containsKey(":createtime")){
//				hql += " and DATE_FORMAT(f.createtime,'%Y-%m-%d') >= '".concat(parameters.get(":createtime").toString()).concat("'");
				hql += " and f.createtime >= '".concat(parameters.get(":createtime").toString()).concat("'");
			}
			if(parameters.containsKey(":endtime")){
//				hql += " and DATE_FORMAT(f.createtime,'%Y-%m-%d') <= '".concat(parameters.get(":endtime").toString()).concat("'");
				hql += " and f.createtime <= '".concat(parameters.get(":endtime").toString()).concat("'");
			}
			hql += " order by f.createtime desc ";
			return hql;
		}

	 public static String getFlowOrderSqlDownCount (Map<String, Object> parameters){
			String hql = "select count(*) from DownRechargeFlow f left join  f.userFlow u left join  f.downShopProduct dos left join  dos.businessType bu  where f.status != 0 ";

			if(parameters.containsKey(":operatoruid")){
				hql += " and f.operators ='".concat(parameters.get(":operatoruid").toString()).concat("' ");
				parameters.remove(":operatoruid");
			}
			if(parameters.containsKey(":useruuid")){
				hql += " and u.useruuid ='".concat(parameters.get(":useruuid").toString()).concat("' ");
				parameters.remove(":useruuid");
			}
			if(parameters.containsKey(":busuuid")){
				hql += " and bu.busuuid ='".concat(parameters.get(":busuuid").toString()).concat("' ");
				parameters.remove(":busuuid");
			}
			if(parameters.containsKey(":provinceuuid")){
				hql += " and f.province ='".concat(parameters.get(":provinceuuid").toString()).concat("' ");
				parameters.remove(":provinceuuid");
			}
			if(parameters.containsKey(":downreqid")){
				hql += " and f.downreqid = '".concat(parameters.get(":downreqid").toString()).concat("' ");
				parameters.remove(":downreqid");
			}
			if(parameters.containsKey(":phoneNum")){
				hql += " and f.dowonphonenum = '".concat(parameters.get(":phoneNum").toString().trim()).concat("' ");
				parameters.remove(":phoneNum");
			}
			if(parameters.containsKey(":downorderuuid")){
				hql += " and f.downorderuuid = '".concat(parameters.get(":downorderuuid").toString().trim()).concat("' ");
				parameters.remove(":downorderuuid");
			}
			if(parameters.containsKey(":status")){
				hql += " and f.status = '".concat(parameters.get(":status").toString().trim()).concat("' ");
				parameters.remove(":status");
			}
			if(parameters.containsKey(":callstatus")){
				hql += " and f.callStatus = ".concat(parameters.get(":callstatus").toString().trim()).concat(" ");
				parameters.remove(":callstatus");
			}
			if(parameters.containsKey(":downfacevalue")){
				hql += " and dos.downfacevalue = ".concat(parameters.get(":downfacevalue").toString().trim()).concat(" ");
				parameters.remove(":downfacevalue");
			}

			if(parameters.containsKey(":createtime")){
//				hql += " and DATE_FORMAT(f.createtime,'%Y-%m-%d') >= '".concat(parameters.get(":createtime").toString()).concat("'");
				hql += " and f.createtime >= '".concat(parameters.get(":createtime").toString()).concat("'");
				parameters.remove(":createtime");
			}
			if(parameters.containsKey(":endtime")){
//				hql += " and DATE_FORMAT(f.createtime,'%Y-%m-%d') <= '".concat(parameters.get(":endtime").toString()).concat("'");
				hql += " and f.createtime <= '".concat(parameters.get(":endtime").toString()).concat("'");
				parameters.remove(":endtime");
			}
			hql += " order by f.createtime desc ";
			return hql;
		}




	/**
	 *  订单导出
	 * @param parameter
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static String exportUpOrder(IUporderService orderService,Map<String, Object> parameter,BillOrderService billOrderService) {
		UporderServiceImpl serviceImpl = (UporderServiceImpl) orderService;

		String inputFileName = CookieRedis.get("exportPath");
		SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		String now = dateFormat.format(new Date());
		//导出文件路径
		String basePath = inputFileName;
		//文件名
		String exportFileName = "up_"+now+".csv";

		BillOrder order = new BillOrder();
		order.setCreateTime(DateUtils.getNowDate());
		order.setPath(CookieRedis.get("download").concat(File.separator)+(basePath + exportFileName).replace(CookieRedis.get("exportPath"), ""));
		order.setType(2);
		order.setFileType(Config.order);
		billOrderService.saveBill(order);
		syncExprotUp(orderService,parameter,billOrderService,order,basePath + exportFileName,serviceImpl);
		return basePath + exportFileName;
	}

		/**
		 * 上游订单
		 * @param orderService
		 * @param parameter
		 * @param billOrderService
		 * @param orderBill
		 * @param path
		 * @param serviceImpl
		 */
		private static void syncExprotUp(IUporderService orderService,final Map<String, Object> parameter,BillOrderService billOrderService,final BillOrder orderBill,
										 final String path, UporderServiceImpl serviceImpl){
			new Thread(new Runnable() {
				@Override
				public void run() {
					try {
						CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader("编号", "供货商", "订单号", "运营商", "省份", "业务类型", "产品编码", "充值号码", "实际金额", "扣款金额", "面值", "折扣", "状态", "充值时间", "完成时间", "备注");
						FileOutputStream fos = new FileOutputStream(path);
						OutputStreamWriter osw = new OutputStreamWriter(fos, "GBK");

						CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat);
						int start = 0;
						int pageSize = Integer.valueOf(CookieRedis.get("exportPageSize"));
						Page page = new Page();
						Map<String, Object> mapResult = MapUtil.getMap();
						mapResult.putAll(parameter);
						long totalCount = serviceImpl.findCountResult(getPageSqlCountUp(mapResult), MapUtil.getMap());

						page.setPageSize(totalCount > pageSize ? pageSize : (int) totalCount);
						UpOrder name = null;
						int count = (int) (totalCount / pageSize) <= 0 ? 1 : (int) (totalCount / pageSize) + 1;
						for (int j = 0; j < count; j++) {
							start = start + 1;
							page.setStart(start);
							List<UpOrder> orders = PaginationUtil.queryPaginationNotCount(getPageSql(parameter), page, serviceImpl.getSessionFactory().openSession()).getResult();
							//插入需导出的数据
							for (int i = 0; i < orders.size(); i++) {
								if(j == 0){
									name = orders.get(i);
								}
								UpOrder order = orders.get(i);
								csvPrinter.printRecord((j * pageSize) + i + 1, order.getUserOrder().getUsername(), order.getUporderuuid(), order.getOperators().equals("2edb5b9c-9f99-11e7-9dc0-fcaa1456507e")
												? "中国移动" : order.getOperators().equals("2ee16927-9f99-11e7-9dc0-fcaa1456507e")
												? "中国联通" : order.getOperators().equals("2ee70961-9f99-11e7-9dc0-fcaa1456507e")
												? "中国电信" : "", order.getProvince(), order.getUpShopPro().getBusinessType().getBusname(), order.getUpShopPro().getProductCode(), order.getUpphonenum() + "", order.getRechargeamount() + "", order.getDiscountAmount() + "", (order.getUpShopPro().getBusinessType().getBusname().equals(Config.telephonefare[0]) || order.getUpShopPro().getBusinessType().getBusname().equals("固话")) ? order.getUpShopPro().getUpfacevalue().concat("话费") : order.getUpShopPro().getUpfacevalue().concat("M"), order.getDiscount() + "", order.getStatus() == 1 ? "充值中" : order.getStatus() == 2 ? "充值成功" : order.getStatus() == 3 ? "充值失败" : order.getStatus() == 4 ? "成功后取消的订单" :
												order.getStatus() == 5 ? "等待充值" : order.getStatus() == 6 ? "退款成功" :
														order.getStatus() == 7 ? "退款失败" : order.getStatus() == 8 ? "失败后取消的订单" :
																order.getStatus() == 10 ? "压单" :
																		order.getStatus() == 11 ? "人工" : "未知", DateUtils.toLongDate(order.getCreatetime()),
										order.getUpendtime() != null ? DateUtils.toLongDate(order.getUpendtime()) : "", order.getRemark());


							}
						}
						csvPrinter.flush();
						csvPrinter.close();
						System.out.println("导出2007文件成功！文件导出路径：--" + path );
						orderBill.setShopId(name.getUserOrder().getUseruuid());
						orderBill.setName(name.getUserOrder().getUsername());
						billOrderService.updateBill(orderBill);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}).start();
		}


		 private static String getPageSqlCountUp(Map<String, Object> parameters) {
				String hql = "select count(*) from UpOrder u left join  u.userOrder left join  u.information pi  left join  u.upShopPro p "
						+ " left join  p.businessType bt left join  u.downRechargeFlow where 1 = 1  ";

				if (parameters.containsKey(":status")) {
					hql += " and u.status = " + parameters.get(":status").toString();
					parameters.remove(":status");
				}
				if (parameters.containsKey(":busuuid")) {
					hql += " and bt.busuuid = '".concat(parameters.get(":busuuid").toString())
							.concat("'");
					parameters.remove(":busuuid");
				}
				if (parameters.containsKey(":productCode")) {
					hql += " and p.productCode = '".concat(parameters.get(":productCode").toString())
							.concat("'");
					parameters.remove(":productCode");
				}
				if (parameters.containsKey(":province")) {
					hql += " and u.province = '".concat(parameters.get(":province").toString())
							.concat("'");
					parameters.remove(":province");
				}

				if (parameters.containsKey(":uporderuuid")) {
					hql += " and u.uporderuuid = '".concat(parameters.get(":uporderuuid").toString())
							.concat("'");
					parameters.remove(":uporderuuid");
				}

				 if (parameters.containsKey(":operatoruid")) {
					 hql += " and u.operators = '".concat(parameters.get(":operatoruid").toString())
							 .concat("'");
					 parameters.remove(":operatoruid");
				 }


				if (parameters.containsKey(":useruuid")) {
					hql += " and u.userOrder.useruuid = '".concat(parameters.get(":useruuid").toString())
							.concat("'");
					parameters.remove(":useruuid");
				}

				if (parameters.containsKey(":phoneNum")) {
					hql += " and u.upphonenum = '".concat(parameters.get(":phoneNum").toString())
							.concat("'");
					parameters.remove(":phoneNum");
				}

//				if (parameters.containsKey(":phoneNum")) {
//					hql += " and u.upphonenum >= '".concat(parameters.get(":phoneNum").toString())
//							.concat("'");
//					parameters.remove(":phoneNum");
//				}

				if (parameters.containsKey(":callBack")) {
					hql += " and u.callBack = " + parameters.get(":callBack").toString();
					parameters.remove(":callBack");
				}

				 if (parameters.containsKey(":downShopName")) {
					 hql += " and u.downShopName = '" + parameters.get(":downShopName").toString().concat("'");
					 parameters.remove(":downShopName");
				 }

				if (parameters.containsKey(":upfacevalue")) {
					hql += " and u.rechargeamount = '".concat(parameters.get(":upfacevalue").toString())
							.concat("'");
					parameters.remove(":upfacevalue");
				}
				if (parameters.containsKey(":createtime")) {
//					hql += " and DATE_FORMAT(u.createtime,'%Y-%m-%d') >= '".concat(parameters.get(":createtime").toString())
					hql += " and u.createtime >= '".concat(parameters.get(":createtime").toString())
							.concat("'");
					parameters.remove(":createtime");
				}
				if (parameters.containsKey(":endtime")) {
//					hql += " and DATE_FORMAT(u.createtime,'%Y-%m-%d') <= '".concat(parameters.get(":endtime").toString())
					hql += " and u.createtime <= '".concat(parameters.get(":endtime").toString())

							.concat("'");
					parameters.remove(":endtime");
				}


				hql += " order by u.createtime desc";
				return hql;
			}

		 private static String getPageSql(Map<String, Object> parameters) {
				String hql = "select u from UpOrder u left join fetch u.userOrder left join fetch u.information pi left join fetch u.upShopPro p "
						+ " left join fetch p.businessType bt where 1=1 ";

				if (parameters.containsKey(":status")) {
					hql += " and u.status =" + parameters.get(":status").toString();
				}
				if (parameters.containsKey(":busuuid")) {
					hql += " and bt.busuuid = '".concat(parameters.get(":busuuid").toString())
							.concat("'");
				}
				if (parameters.containsKey(":productCode")) {
					hql += " and p.productCode = '".concat(parameters.get(":productCode").toString())
							.concat("'");
				}
				if (parameters.containsKey(":province")) {
					hql += " and u.province = '".concat(parameters.get(":province").toString())
							.concat("'");
					parameters.remove(":province");
				}
				if (parameters.containsKey(":uporderuuid")) {
					hql += " and u.uporderuuid = '".concat(parameters.get(":uporderuuid").toString())
							.concat("'");
				}

				if (parameters.containsKey(":useruuid")) {
					hql += " and u.userOrder.useruuid = '".concat(parameters.get(":useruuid").toString())
							.concat("'");
				}

				if (parameters.containsKey(":phoneNum")) {
					hql += " and u.upphonenum = '".concat(parameters.get(":phoneNum").toString())
							.concat("'");
				}

//				if (parameters.containsKey(":phoneNum")) {
//					hql += " and u.upphonenum >= '".concat(parameters.get(":phoneNum").toString())
//							.concat("'");
//				}

				if (parameters.containsKey(":callBack")) {
					hql += " and u.callBack = " + parameters.get(":callBack").toString();
				}

				 if (parameters.containsKey(":downShopName")) {
					 hql += " and u.downShopName = '" + parameters.get(":downShopName").toString().concat("'");;
				 }

				if (parameters.containsKey(":upfacevalue")) {
					hql += " and u.rechargeamount = " + parameters.get(":upfacevalue").toString();
				}
				if (parameters.containsKey(":createtime")) {
					hql += " and u.createtime >= '".concat(parameters.get(":createtime").toString())
							.concat("'");
				}
				if (parameters.containsKey(":endtime")) {
					hql += " and u.createtime <= '".concat(parameters.get(":endtime").toString())
							.concat("'");
				}

				hql += " order by u.createtime desc";
				return hql;
			}

	/**
	 * 下游统计
	 * @param array
	 * @return
	 */
	 public static String exportDown(JSONArray array){
		    String  inputFileName = CookieRedis.get("exportPath");
		    SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		    String now = dateFormat.format(new Date());
		    //导出文件路径
		    String basePath = inputFileName;
		    //文件名
		    String exportFileName = "down_"+now+".xlsx";

		    String[] cellTitle = {"编号","渠道名称","业务类型","运营商","省份","总订单金额","成功金额","失败金额","等待充值金额","交易笔数","成功笔数","失败笔数","成功率","等待订单数量"};
		    // 声明一个工作薄
//		    XSSFWorkbook workBook = null;
//		    workBook = new XSSFWorkbook();
		    try {
				XSSFWorkbook workBook = new XSSFWorkbook();
				SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workBook, 1000);
				// 生成一个表格
				Sheet sheet = sxssfWorkbook.createSheet();
				workBook.setSheetName(0,"订单统计");
				// 创建表格标题行 第一行
				Row titleRow = sheet.createRow(0);
				for(int i=0;i<cellTitle.length;i++){
				    titleRow.createCell(i).setCellValue(cellTitle[i]);
				}
				//插入需导出的数据
				for(int i= 0;i < array.size();i++){
				    Row row = sheet.createRow(i+1);
				    row.createCell(0).setCellValue(i+1);
				    JSONObject obj = array.getJSONObject(i);
				    row.createCell(1).setCellValue(obj.getString("username"));
				    row.createCell(2).setCellValue(obj.getString("busname"));
				    row.createCell(3).setCellValue(obj.getString("operatorname"));
				    row.createCell(4).setCellValue(obj.getString("provincename"));
				    row.createCell(5).setCellValue(obj.getString("sumamount"));
				    row.createCell(6).setCellValue(obj.getString("success"));
				    row.createCell(7).setCellValue(obj.getString("failt"));
				    row.createCell(8).setCellValue(obj.getString("wait"));
				    row.createCell(9).setCellValue(obj.getString("sumorder"));
				    row.createCell(10).setCellValue(obj.getString("successorder"));
				    row.createCell(11).setCellValue(obj.getString("failtorder"));
				    row.createCell(12).setCellValue("");
				    row.createCell(13).setCellValue(obj.getString("waitorder"));

				}
				File  file = new File(basePath+exportFileName);
				//文件输出流
				FileOutputStream outStream;
				try {
					outStream = new FileOutputStream(file);
					sxssfWorkbook.write(outStream);
					    outStream.flush();
					    outStream.close();
					    System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
					    return basePath+exportFileName;
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return null;
	 }

	 /**
	  * 上游产品统计
	  * @param array
	  * @return
	  */
	 public static String exportUpFlow(JSONArray array){
		 String  inputFileName = CookieRedis.get("exportPath");
		    SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		    String now = dateFormat.format(new Date());
		    //导出文件路径
		    String basePath = inputFileName;
		    //文件名
		    String exportFileName = "up_"+now+".xlsx";

		    String[] cellTitle = {"编号","渠道名称","产品名称","总交易笔数","总原价","总售价","成功笔数","成功原价","成功售价","失败笔数","失败原价","失败售价"};
		    // 声明一个工作薄
		    XSSFWorkbook workBook = null;
		    workBook = new XSSFWorkbook();
		    // 生成一个表格
		    XSSFSheet sheet = workBook.createSheet();
		    workBook.setSheetName(0,"订单统计");
		    // 创建表格标题行 第一行
		    XSSFRow titleRow = sheet.createRow(0);
		    for(int i=0;i<cellTitle.length;i++){
		        titleRow.createCell(i).setCellValue(cellTitle[i]);
		    }
		    //插入需导出的数据
		    for(int i= 0;i < array.size();i++){
		        XSSFRow row = sheet.createRow(i+1);
		        row.createCell(0).setCellValue(i+1);
		        JSONObject obj = array.getJSONObject(i);
		        row.createCell(1).setCellValue(obj.getString("username"));
		        row.createCell(2).setCellValue(obj.getString("proname"));

		        row.createCell(3).setCellValue(obj.getString("sumcount"));
		        row.createCell(4).setCellValue(obj.getString("summoney"));
		        row.createCell(5).setCellValue(obj.getString("sumdis"));

		        row.createCell(6).setCellValue(obj.getString("successnum"));
		        row.createCell(7).setCellValue(obj.getString("sucmoney"));
		        row.createCell(8).setCellValue(obj.getString("sudis"));

		        row.createCell(9).setCellValue(obj.getString("failtnum"));
		        row.createCell(10).setCellValue(obj.getString("famoney"));
		        row.createCell(11).setCellValue(obj.getString("failtdis"));

//		        row.createCell(3).setCellValue(obj.getString("count"));
//		        row.createCell(4).setCellValue(obj.getString("success"));
//		        row.createCell(5).setCellValue(obj.getString("failt"));
//		        row.createCell(6).setCellValue(obj.getString("sumamount"));
//		        row.createCell(7).setCellValue(obj.getString("sm"));
//		        row.createCell(8).setCellValue(obj.getString("fm"));
//		        row.createCell(9).setCellValue(obj.getString("profit"));
		    }
		    File  file = new File(basePath+exportFileName);
		    //文件输出流
		    FileOutputStream outStream;
			try {
				outStream = new FileOutputStream(file);
				 workBook.write(outStream);
				    outStream.flush();
				    outStream.close();
				    System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
				    return basePath+exportFileName;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return null;
	 }

	 /**
	  * 下游产品统计
	  * @param array
	  * @return
	  */
	 public static String exportDownFlowJSON(JSONArray array){
		 String  inputFileName = CookieRedis.get("exportPath");
		    SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		    String now = dateFormat.format(new Date());
		    //导出文件路径
		    String basePath = inputFileName;
		    //文件名
		    String exportFileName = "down_"+now+".xlsx";

		    String[] cellTitle = {"编号","渠道名称","产品名称","总交易笔数","总原价","总售价","成功笔数","成功原价","成功售价","失败笔数","失败原价","失败售价"};
		    // 声明一个工作薄
		    XSSFWorkbook workBook = null;
		    workBook = new XSSFWorkbook();
		    // 生成一个表格
		    XSSFSheet sheet = workBook.createSheet();
		    workBook.setSheetName(0,"订单统计");
		    // 创建表格标题行 第一行
		    XSSFRow titleRow = sheet.createRow(0);
		    for(int i=0;i<cellTitle.length;i++){
		        titleRow.createCell(i).setCellValue(cellTitle[i]);
		    }
		    //插入需导出的数据
		    for(int i= 0;i < array.size();i++){
		        XSSFRow row = sheet.createRow(i+1);
		        row.createCell(0).setCellValue(i+1);
		        JSONObject obj = array.getJSONObject(i);

		        row.createCell(1).setCellValue(obj.getString("username"));
		        row.createCell(2).setCellValue(obj.getString("proname"));

		        row.createCell(3).setCellValue(obj.getString("sumcount"));
		        row.createCell(4).setCellValue(obj.getString("summoney"));
		        row.createCell(5).setCellValue(obj.getString("sumdis"));

		        row.createCell(6).setCellValue(obj.getString("successnum"));
		        row.createCell(7).setCellValue(obj.getString("sucmoney"));
		        row.createCell(8).setCellValue(obj.getString("sudis"));

		        row.createCell(9).setCellValue(obj.getString("failtnum"));
		        row.createCell(10).setCellValue(obj.getString("famoney"));
		        row.createCell(11).setCellValue(obj.getString("failtdis"));
		    }
		    File  file = new File(basePath+exportFileName);
		    //文件输出流
		    FileOutputStream outStream;
			try {
				outStream = new FileOutputStream(file);
				 workBook.write(outStream);
				    outStream.flush();
				    outStream.close();
				    System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
				    return basePath+exportFileName;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return null;
	 }

	 public static String exportUp(JSONArray array){
		 String  inputFileName = CookieRedis.get("exportPath");
		    SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		    String now = dateFormat.format(new Date());
		    //导出文件路径
		    String basePath = inputFileName;
		    //文件名
		    String exportFileName = "up_"+now+".xlsx";

		    String[] cellTitle = {"编号","渠道名称","业务类型","运营商","省份","总订单金额","成功金额","失败金额","等待充值金额","交易笔数","成功笔数","失败笔数","成功率","等待订单数量","利润"};
		    // 声明一个工作薄
		    XSSFWorkbook workBook = null;
		    workBook = new XSSFWorkbook();
		    // 生成一个表格
		    XSSFSheet sheet = workBook.createSheet();
		    workBook.setSheetName(0,"订单统计");
		    // 创建表格标题行 第一行
		    XSSFRow titleRow = sheet.createRow(0);
		    for(int i=0;i<cellTitle.length;i++){
		        titleRow.createCell(i).setCellValue(cellTitle[i]);
		    }
		    //插入需导出的数据
		    for(int i= 0;i < array.size();i++){
		        XSSFRow row = sheet.createRow(i+1);
		        row.createCell(0).setCellValue(i+1);
		        JSONObject obj = array.getJSONObject(i);
		        row.createCell(1).setCellValue(obj.getString("username"));
		        row.createCell(2).setCellValue(obj.getString("busname"));
		        row.createCell(3).setCellValue(obj.getString("operatorname"));
		        row.createCell(4).setCellValue(obj.getString("provincename"));
		        row.createCell(5).setCellValue(obj.getString("sumamount"));
		        row.createCell(6).setCellValue(obj.getString("success"));
		        row.createCell(7).setCellValue(obj.getString("failt"));
		        row.createCell(8).setCellValue(obj.getString("wait"));
		        row.createCell(9).setCellValue(obj.getString("sumorder"));
		        row.createCell(10).setCellValue(obj.getString("successorder"));
		        row.createCell(11).setCellValue(obj.getString("failtorder"));
		        row.createCell(12).setCellValue("");
		        row.createCell(13).setCellValue(obj.getString("waitorder"));
		        row.createCell(14).setCellValue(obj.getString("profit"));
		    }
		    File  file = new File(basePath+exportFileName);
		    //文件输出流
		    FileOutputStream outStream;
			try {
				outStream = new FileOutputStream(file);
				 workBook.write(outStream);
				    outStream.flush();
				    outStream.close();
				    System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
				    return basePath+exportFileName;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return null;
	 }


	 public static void main(String[] args) {
	//  private static HSSFWorkbook workbook = new HSSFWorkbook();
//	      private static HSSFSheet sheet = workbook.createSheet();
//	  HSSFWorkbook workbook = new HSSFWorkbook();
//	        HSSFSheet sheet = workbook.createSheet();
//	  ExportExcelUtil eeu = new ExportExcelUtil(workbook, sheet);
//
//	  String[] strArr = new String[] { "序号", "姓名", "性 别", "出生年月", "民族", "籍贯", "备注" };
//	  int colNum = strArr.length;
//
//	  int rowNO = 0;
//	  //1. titleCaption
//	  eeu.createExcelRow(workbook, sheet, rowNO, -1, colNum, "统计报表"); // , 250, "bold", "center"
//	  //2.
//	  rowNO++;
//	  eeu.createExcelRow(workbook, sheet, rowNO, 200, colNum,
//	    " 制 表 人: 赵小明       制 表 日 期: " + new java.text.SimpleDateFormat("yyyy-MM-dd").format(new java.util.Date()), 180, "normal", "right");
//	  //3.columnTitleHeader
//	  rowNO++;
//	  eeu.createColumnHeader(sheet, rowNO, 300, strArr);
//
//	  //4.数据行     循环创建中间的单元格的各项的值
//	  rowNO++;
//	  String[][] columnData = new String[][]{{ "1", "zhangsan", "男", "1985-10-06 21:00:00", "汉族", "西安", "学生" },
//	    { "2", "猪猪", "女", "出生年月", "民族", "籍贯", "备注" },
//	    { "3", "明明", "男", "1980-07-08", "汉族", "西安", "学生" },
//	    { "4", "光光", "女", "1985-06-30", "汉族", "西安", "学生" },
//	    { "5", "先民", "男", "1987-06-06", "汉族", "西安", "学生" },
//	    { "6", "数据", "女", "1985-04-06", "汉族", "西安", "学生" },
//	    { "7", "历史", "女", "1985-06-06", "汉族", "西安", "学生" },
//	    { "8", "妩媚", "女", "1990-10-06", "汉族", "西安", "学生" },
//	    { "9", "李斯", "男", "1985-06-20", "汉族", "西安", "学生" },
//	    { "10", "犀利", "女", "1985-06-06", "汉族", "西安", "学生" },
//	    { "11", "绅士", "男", "1985-01-06", "汉族", "西安", "学生" },
//	    { "12", "先知", "女", "1984-06-06", "汉族", "西安", "学生" },
//	    { "13", "精明", "男", "1985-06-12", "汉族", "西安", "学生" },
//	    { "14", "科技", "男", "1972-02-03", "汉族", "西安", "学生" },
//	    { "15", "软件", "女", "1985-02-06", "汉族", "西安", "学生" },
//	    { "16", "世道", "男", "1999-03-06", "汉族", "西安", "学生" },
//	    { "17", "明了", "女", "1985-06-06", "汉族", "西安", "学生" },
//	    { "18", "小明", "男", "1984-06-09", "汉族", "西安", "学生" },
//	    { "19", "高娟", "女", "1985-03-06", "汉族", "西安", "学生" },
//	    { "20", "小莉", "女", "1987-12-25", "汉族", "西安", "学生" }} ;
//	  sheet = eeu.createColumnData(sheet, rowNO,  columnData, 20);
//	        eeu.createSummaryRow(workbook, sheet, colNum, "合计：" + columnData.length, 180, "normal", "right");
//	       String  inputFileName = "C:\\Users\\luoyang\\Desktop\\exportdatadown.xlsx";
//	       String  outputFileName = "C:\\Users\\luoyang\\Desktop\\exportdatadownsss.xlsx";
//	      List dataList = new ArrayList<>();
//	      dataList.add("ddd");
//	      dataList.add("ddd");
//	  try {
////		eeu.exportExcelFile(inputFileName, outputFileName, dataList);
//	} catch (Exception e) {
//		// TODO Auto-generated catch block
//		e.printStackTrace();
//	}
//

		String  inputFileName = CookieRedis.get("exportPath");
	    SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
	    String now = dateFormat.format(new Date());
	    //导出文件路径
	    String basePath = inputFileName;
	    //文件名
	    String exportFileName = "分销商_"+now+".xlsx";

	    String[] cellTitle = {"渠道名称","业务类型","运营商","省份","总订单金额","成功金额","失败金额","等待充值金额","交易笔数","成功笔数","失败笔数","成功率","等待订单数量"};
	    //需要导出的数据
	    List<String[]> dataList = new ArrayList<String[]>();
	    dataList.add(new String[]{"东邪","17232401001","男","2015年9月"});
	    dataList.add(new String[]{"西毒","17232401002","女","2016年9月"});
	    dataList.add(new String[]{"南帝","17232401003","男","2017年9月"});
	    dataList.add(new String[]{"北丐","17232401004","男","2015年9月"});
	    dataList.add(new String[]{"中神通","17232401005","女","2017年9月"});
	    // 声明一个工作薄
	    XSSFWorkbook workBook = null;
	    workBook = new XSSFWorkbook();
	    // 生成一个表格
	    XSSFSheet sheet = workBook.createSheet();
	    workBook.setSheetName(0,"学生信息");
	    // 创建表格标题行 第一行
	    XSSFRow titleRow = sheet.createRow(0);
	    for(int i=0;i<cellTitle.length;i++){
	        titleRow.createCell(i).setCellValue(cellTitle[i]);
	    }
	    //插入需导出的数据
	    for(int i=0;i<dataList.size();i++){
	        XSSFRow row = sheet.createRow(i+1);
	        row.createCell(0).setCellValue(i+1);
	        row.createCell(1).setCellValue(dataList.get(i)[0]);
	        row.createCell(2).setCellValue(dataList.get(i)[1]);
	        row.createCell(3).setCellValue(dataList.get(i)[2]);
	        row.createCell(4).setCellValue(dataList.get(i)[3]);
	    }
	    File  file = new File(basePath+exportFileName);
	    //文件输出流
	    FileOutputStream outStream;
		try {
			outStream = new FileOutputStream(file);
			 workBook.write(outStream);
			    outStream.flush();
			    outStream.close();
			    System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


	 }

	public static String exportUpProduct(List<UpShopProduct> exportUpProduct) {
		String  inputFileName = CookieRedis.get("exportPath");
		SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		String now = dateFormat.format(new Date());
		//导出文件路径
		String basePath = inputFileName;
		//文件名
		String exportFileName = "up_product_"+now+".xlsx";

		String[] cellTitle = {"编号","产品编码","面值","标准价","折后价","折扣"};
		// 声明一个工作薄
		XSSFWorkbook workBook = null;
		workBook = new XSSFWorkbook();
		// 生成一个表格
		XSSFSheet sheet = workBook.createSheet();
		workBook.setSheetName(0,"上游产品");
		// 创建表格标题行 第一行
		XSSFRow titleRow = sheet.createRow(0);
		for(int i=0;i<cellTitle.length;i++){
			titleRow.createCell(i).setCellValue(cellTitle[i]);
		}
		//插入需导出的数据
		for(int i= 0;i < exportUpProduct.size();i++){
			XSSFRow row = sheet.createRow(i+1);
			row.createCell(0).setCellValue(i+1);
			UpShopProduct product = exportUpProduct.get(i);
			row.createCell(1).setCellValue(product.getUpprouuid());
			row.createCell(2).setCellValue(product.getBusinessType().getBusname().equals(Config.faceValue[0]) ? product.getUpfacevalue().concat(Config.faceValue[0]) : product.getUpfacevalue().concat(Config.faceValue[1]));
			row.createCell(3).setCellValue(product.getPrice().toString());
			row.createCell(4).setCellValue(product.getDiscountAmount().toString());
			row.createCell(5).setCellValue(product.getDiscount().toString());
		}
		File  file = new File(basePath+exportFileName);
		//文件输出流
		FileOutputStream outStream;
		try {
			outStream = new FileOutputStream(file);
			 workBook.write(outStream);
				outStream.flush();
				outStream.close();
				System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
				return basePath+exportFileName;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}


	public static String exportdataRechargeRecord(Page<DownShopRecharge> pageDownShopRecharge) {
		String  inputFileName = CookieRedis.get("exportPath");
		SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		String now = dateFormat.format(new Date());
		//导出文件路径
		String basePath = inputFileName;
		//文件名
		String exportFileName = "下游充值加款记录_"+now+".xlsx";

		String[] cellTitle = {"编号","充值渠道商名称","充值金额","充值时间","充值类型","充值状态","备注"};
		// 声明一个工作薄
		XSSFWorkbook workBook = null;
		workBook = new XSSFWorkbook();
		// 生成一个表格
		XSSFSheet sheet = workBook.createSheet();
		workBook.setSheetName(0,"上游产品");
		// 创建表格标题行 第一行
		XSSFRow titleRow = sheet.createRow(0);
		for(int i=0;i<cellTitle.length;i++){
			titleRow.createCell(i).setCellValue(cellTitle[i]);
		}
		List<DownShopRecharge> rechargeLsit = pageDownShopRecharge.getResult();
		//插入需导出的数据
		for(int i= 0;i < rechargeLsit.size();i++){
			XSSFRow row = sheet.createRow(i+1);
			row.createCell(0).setCellValue(i+1);
			DownShopRecharge recharge = rechargeLsit.get(i);
			row.createCell(1).setCellValue(recharge.getUser().getUsername());
			row.createCell(2).setCellValue(recharge.getRechargeamount().toString());
			row.createCell(3).setCellValue(recharge.getRechargetime());
			row.createCell(4).setCellValue(recharge.getPayType() == 1 ? "充值" : "扣款");
			row.createCell(5).setCellValue(recharge.getRechargestatus().equals(1) ? "充值成功" : "");
			row.createCell(6).setCellValue(recharge.getRemark());
		}
		File  file = new File(basePath+exportFileName);
		//文件输出流
		FileOutputStream outStream;
		try {
			outStream = new FileOutputStream(file);
			workBook.write(outStream);
			outStream.flush();
			outStream.close();
			System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
			return basePath+exportFileName;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;

	}

	public static String exportUpRechargeRecord(Page<UpRechargeRecord> pageUprechargerecord) {
		String  inputFileName = CookieRedis.get("exportPath");
		SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		String now = dateFormat.format(new Date());
		//导出文件路径
		String basePath = inputFileName;
		//文件名
		String exportFileName = "上游充值加款记录_"+now+".xlsx";

		String[] cellTitle = {"编号","充值渠道商名称","充值金额","充值时间","充值类型","充值状态","备注"};
		// 声明一个工作薄
		XSSFWorkbook workBook = null;
		workBook = new XSSFWorkbook();
		// 生成一个表格
		XSSFSheet sheet = workBook.createSheet();
		workBook.setSheetName(0,"上游产品");
		// 创建表格标题行 第一行
		XSSFRow titleRow = sheet.createRow(0);
		for(int i=0;i<cellTitle.length;i++){
			titleRow.createCell(i).setCellValue(cellTitle[i]);
		}
		List<UpRechargeRecord> rechargeLsit = pageUprechargerecord.getResult();
		//插入需导出的数据
		for(int i= 0;i < rechargeLsit.size();i++){
			XSSFRow row = sheet.createRow(i+1);
			row.createCell(0).setCellValue(i+1);
			UpRechargeRecord recharge = rechargeLsit.get(i);
			row.createCell(1).setCellValue(recharge.getUser().getUsername());
			row.createCell(2).setCellValue(recharge.getRechargeamount().toString());
			row.createCell(3).setCellValue(recharge.getRechargetime());
			row.createCell(4).setCellValue(recharge.getPayType() == 1 ? "充值" : "扣款");
			row.createCell(5).setCellValue(recharge.getRechargestatus().equals(1) ? "充值成功" : "");
			row.createCell(6).setCellValue(recharge.getRemark());
		}
		File  file = new File(basePath+exportFileName);
		//文件输出流
		FileOutputStream outStream;
		try {
			outStream = new FileOutputStream(file);
			workBook.write(outStream);
			outStream.flush();
			outStream.close();
			System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
			return basePath+exportFileName;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
//	public static String exportWxOrder(SlowOrderService slowOrderService, Map<String, Object> parameters) {
//		SlowOrderServiceImpl serviceImpl = (SlowOrderServiceImpl) slowOrderService;
//
//		String inputFileName = CookieRedis.get("exportPath");
//		SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
//		String now = dateFormat.format(new Date());
//		//导出文件路径
//		String basePath = inputFileName;
//		//文件名
//		String exportFileName = "骑士卡_"+now+".xlsx";
//
//		String[] cellTitle = {"电话号码","面值","微信支付单号","充值金额","付款金额","支付状态","充值状态","支付时间","备注"};
//		// 声明一个工作薄
//		try {
//			XSSFWorkbook workBook = new XSSFWorkbook();
//			SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workBook, 1000);
//			// 生成一个表格
//			// 生成一个表格
//			Sheet sheet = sxssfWorkbook.createSheet();
//			workBook.setSheetName(0,"慢充订单");
//			// 创建表格标题行 第一行
//			Row titleRow = sheet.createRow(0);
//			for(int i=0;i<cellTitle.length;i++){
//				titleRow.createCell(i).setCellValue(cellTitle[i]);
//			}
//
//			int start = 0 ;
//			int pageSize = Integer.valueOf(CookieRedis.get("exportPageSize"));
//			Page page = new Page();
//			Map<String, Object> mapResult = MapUtil.getMap();
//			mapResult.putAll(parameters);
//			long totalCount =  serviceImpl.findCountResult(getWxSlowOrderSqlCount(parameters) , MapUtil.getMap());
//
//			page.setPageSize(totalCount > pageSize ? pageSize : (int)totalCount);
//
//			int count = (int) (totalCount / pageSize) <= 0 ? 1 : (int) (totalCount / pageSize) + 1;
//
//			for(int j = 0 ; j < count ; j++){
//				start = start+1;
//				page.setStart(start);
//				//判断最后一页
//				List<SlowOrder> orders = PaginationUtil.queryPaginationNotCount(getWxSlowOrderSql(mapResult), page, serviceImpl.getSessionFactory().openSession()).getResult();
//				//插入需导出的数据
//				for(int i= 0;i < orders.size();i++){
//					Row row = sheet.createRow( (j * pageSize) + i  +1);
////			        row.createCell(0).setCellValue((j * pageSize) + i  +1);
//					SlowOrder slow = orders.get(i);
//					row.createCell(0).setCellValue(slow.getPhoneNum());//flow.getUserFlow().getUsername()
//					row.createCell(1).setCellValue(slow.getPrice().toString());
////				    {"电话号码","面值","微信支付单号","充值金额","付款金额","支付状态","充值状态","支付时间","备注"};
//					row.createCell(2).setCellValue(slow.getWxOrder() == null ? "" : slow.getWxOrder());
//					row.createCell(3).setCellValue(slow.getPayPrice() == null ? "" : slow.getPayPrice().toString());
//					row.createCell(4).setCellValue(slow.getCashFee() == null ? 0 : Integer.valueOf(slow.getCashFee()) / 100);
//					row.createCell(5).setCellValue(slow.getPayState() == 1 ? "支付中" : slow.getPayState() == 2 ? "支付成功" : slow.getPayState() == 3 ? "支付失败": "未知");//微信支付状态 1支付中 2支付成功 3支付失败
////				    充值状态 0 删除 1充值中 2充值成功 3充值失败 4 充值退单 5订单提交 6已撤销 7 被合单 8 被拆单
//					row.createCell(6).setCellValue(slow.getRechargeState() == 1 ? "充值中" : slow.getRechargeState() == 2 ? "充值成功" : slow.getRechargeState() == 3 ? "充值失败" : slow.getRechargeState() == 5 ? "订单到达" : "未知"  );
//					row.createCell(7).setCellValue(DateUtils.toLongDate(slow.getEndTime()));
//					row.createCell(8).setCellValue(slow.getRemark());
//				}
//			}
//			File  file = new File(basePath+exportFileName);
//			//文件输出流
//			FileOutputStream outStream;
//			try {
//				outStream = new FileOutputStream(file);
//				sxssfWorkbook.write(outStream);
//				outStream.flush();
//				outStream.close();
//				System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
//				return basePath+exportFileName;
//			} catch (Exception e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return null;
//	}

	private static String getWxSlowOrderSqlCount( Map<String, Object> parameters){
		String hql = "select count(*) from WeiXinOrder s where (s.rechargeState != 0 )";
		if(parameters.containsKey(":phoneNum")){
			hql += " and s.phoneNum = '"+ parameters.get(":phoneNum")+"'";
		}
		if(parameters.containsKey(":operators")){
			hql += " and s.operators = '"+ parameters.get(":operators")+"'";
		}
		if(parameters.containsKey(":rechargeState")){
			hql += " and s.rechargeState = "+ parameters.get(":rechargeState")+"";
		}
		if(parameters.containsKey(":province")){
			hql += " and s.province = '"+ parameters.get(":province")+"'";
		}
		if(parameters.containsKey(":city")){
			hql += " and s.city = '"+ parameters.get(":city")+"'";
		}
		if(parameters.containsKey(":price")){
			hql += " and s.price = "+ parameters.get(":price")+"";
		}
		if(parameters.containsKey(":isLock")){
			hql += " and s.isLock = "+ parameters.get(":isLock")+"";
		}
		if(parameters.containsKey(":wxOrder")){
			hql += " and s.wxOrder = "+ parameters.get(":wxOrder")+"";
		}
		if(parameters.containsKey(":payState")){
			hql += " and s.payState = "+ parameters.get(":payState")+"";
		}
		if(parameters.containsKey(":payType")){
			hql += " and s.payType = "+ parameters.get(":payType")+"";
		}
		if(parameters.containsKey(":importTime")){
			hql += " and s.commitTime >= '"+ parameters.get(":importTime")+"'";
		}
		if(parameters.containsKey(":endtime")){
			hql += " and s.commitTime <= '".concat(parameters.get(":endtime").toString()).concat("'");
			parameters.remove(":endtime");
		}

		hql += " order by s.importTime desc";
		return hql;
	}

	private static String getWxSlowOrderSql( Map<String, Object> parameters){
		String hql = "select s from WeiXinOrder s where (s.rechargeState != 0 )";
		if(parameters.containsKey(":phoneNum")){
			hql += " and s.phoneNum = '"+ parameters.get(":phoneNum")+"'";
		}
		if(parameters.containsKey(":operators")){
			hql += " and s.operators = '"+ parameters.get(":operators")+"'";
		}
		if(parameters.containsKey(":rechargeState")){
			hql += " and s.rechargeState = "+ parameters.get(":rechargeState")+"";
		}
		if(parameters.containsKey(":province")){
			hql += " and s.province = '"+ parameters.get(":province")+"'";
		}
		if(parameters.containsKey(":city")){
			hql += " and s.city = '"+ parameters.get(":city")+"'";
		}
		if(parameters.containsKey(":price")){
			hql += " and s.price = "+ parameters.get(":price")+"";
		}
		if(parameters.containsKey(":isLock")){
			hql += " and s.isLock = "+ parameters.get(":isLock")+"";
		}
		if(parameters.containsKey(":wxOrder")){
			hql += " and s.wxOrder = "+ parameters.get(":wxOrder")+"";
		}
		if(parameters.containsKey(":payState")){
			hql += " and s.payState = "+ parameters.get(":payState")+"";
		}
		if(parameters.containsKey(":payType")){
			hql += " and s.payType = "+ parameters.get(":payType")+"";
		}
		if(parameters.containsKey(":importTime")){
			hql += " and s.commitTime >= '"+ parameters.get(":importTime")+"'";
		}
		if(parameters.containsKey(":endtime")){
			hql += " and s.commitTime <= '".concat(parameters.get(":endtime").toString()).concat("'");
			parameters.remove(":endtime");
		}
		hql += " order by s.importTime desc";
		return hql;
	}



	public static String exportDownBill(JSONObject jsonObject,DownShopInfo shopInfo,String startTimex,String endtTimex) {
		return writeBillDown(jsonObject,shopInfo,startTimex,endtTimex);
	}

	public static String writeBillDown(JSONObject jsonObject,DownShopInfo shopInfo,String startTimex,String endtTimex){
		String filePath = CookieRedis.get("billModelDown");
		if(StringUtils.isBlank(filePath)){
			return "模板未配置";
		}
		if(StringUtils.isBlank(shopInfo.getUser().getWechatid())){
			return "公司名称没有配置";
		}
		FileInputStream fis =null;
		Workbook wookbook = null;
		try {
			fis = new FileInputStream(filePath);
		} catch(Exception e){
			e.printStackTrace();
		}
		try {
			try {
				wookbook = new HSSFWorkbook(fis);
				//wookbook = new XSSFWorkbook(fis);//得到工作簿
			} catch (Exception ex){
				ex.printStackTrace();
			}
			Sheet sheet = wookbook.getSheetAt(0);

			Row row1 = sheet.getRow(1);
			row1.getCell(0).setCellValue(row1.getCell(0).getStringCellValue().replaceAll("xxxxxxxx",shopInfo.getUser().getWechatid()));
			Row row = sheet.getRow(3);
			Cell cell = row.getCell(0);
			cell.setCellValue("账单周期："+startTimex+"----"+endtTimex);
			Row row6 = sheet.getRow(6);
			Cell cell60 = row6.getCell(0);
			cell60.setCellValue(startTimex+"-"+endtTimex);
			//成功订单
			Cell cell61 = row6.getCell(1);
			cell61.setCellValue(jsonObject.getString("orderNum"));
			//上期余额
			Cell cell62 = row6.getCell(2);
			cell62.setCellValue(jsonObject.getString("sqye"));
			//加款金额
			Cell cell63 = row6.getCell(3);
			cell63.setCellValue(jsonObject.getString("jkje"));
			//消耗金额
			Cell cell64 = row6.getCell(4);
			cell64.setCellValue(jsonObject.getString("xh"));
			//面值
			Cell cell65 = row6.getCell(5);
			cell65.setCellValue(jsonObject.getString("mz"));
			//余额
			Cell cell66 = row6.getCell(6);
			cell66.setCellValue(jsonObject.getString("dqye"));

			//合计 消耗金额
			Row row7 = sheet.getRow(7);
			Cell cell74 = row7.getCell(4);
			cell74.setCellValue(jsonObject.getString("xh"));
			//面值
			Cell cell75 = row7.getCell(5);
			cell75.setCellValue(jsonObject.getString("mz"));

			Cell cell76 = row7.getCell(6);
			cell76.setCellValue(jsonObject.getString("dqye"));

			File  file = new File(CookieRedis.get("exportPath")+"/"+jsonObject.getString("username")+DateUtils.getCurrMonthDay()+".xls");
			//文件输出流
			FileOutputStream outStream;
			outStream = new FileOutputStream(file);
			wookbook.write(outStream);
			outStream.flush();
			outStream.close();
			System.out.println("导出2007文件成功！文件导出路径：--"+file.getAbsolutePath());
			return file.getAbsolutePath();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static String exportUpBill(JSONObject jsonObject,UpShopInfo upShopInfo,String startTimex,String endtTimex) {
		String filePath = CookieRedis.get("billModelUp");
		if(StringUtils.isBlank(filePath)){
			return "模板未配置";
		}
		if(StringUtils.isBlank(upShopInfo.getUser().getWechatid())){
			return "公司名称没有配置";
		}
		FileInputStream fis =null;
		Workbook wookbook = null;
		try {
			fis = new FileInputStream(filePath);
		} catch(Exception e){
			e.printStackTrace();
		}
		try {
			try {
				wookbook = new HSSFWorkbook(fis);
				//wookbook = new XSSFWorkbook(fis);//得到工作簿
			} catch (Exception ex){
				ex.printStackTrace();
			}
			Sheet sheet = wookbook.getSheetAt(0);

			Row row1 = sheet.getRow(1);
			row1.getCell(0).setCellValue(row1.getCell(0).getStringCellValue().replaceAll("xxxxxxxx",upShopInfo.getUser().getWechatid()));
			Row row = sheet.getRow(3);
			Cell cell = row.getCell(0);
			cell.setCellValue("账单周期："+startTimex+"----"+endtTimex);
			Row row6 = sheet.getRow(6);
			Cell cell60 = row6.getCell(0);
			cell60.setCellValue(startTimex+"-"+endtTimex);
			//成功订单
			Cell cell61 = row6.getCell(1);
			cell61.setCellValue(jsonObject.getString("orderNum"));
			//上期余额
			Cell cell62 = row6.getCell(2);
			cell62.setCellValue(jsonObject.getString("sqye"));
			//加款金额
			Cell cell63 = row6.getCell(3);
			cell63.setCellValue(jsonObject.getString("jkje"));
			//消耗金额
			Cell cell64 = row6.getCell(4);
			cell64.setCellValue(jsonObject.getString("xh"));
			//面值
			Cell cell65 = row6.getCell(5);
			cell65.setCellValue(jsonObject.getString("mz"));
			//余额
			Cell cell66 = row6.getCell(6);
			cell66.setCellValue(jsonObject.getString("dqye"));

			//合计 消耗金额
			Row row7 = sheet.getRow(7);
			Cell cell74 = row7.getCell(4);
			cell74.setCellValue(jsonObject.getString("xh"));
			//面值
			Cell cell75 = row7.getCell(5);
			cell75.setCellValue(jsonObject.getString("mz"));

			Cell cell76 = row7.getCell(6);
			cell76.setCellValue(jsonObject.getString("dqye"));

			File  file = new File(CookieRedis.get("exportPath")+"/"+jsonObject.getString("username")+DateUtils.getCurrMonthDay()+".xls");
			//文件输出流
			FileOutputStream outStream;
			outStream = new FileOutputStream(file);
			wookbook.write(outStream);
			outStream.flush();
			outStream.close();
			System.out.println("导出2007文件成功！文件导出路径：--"+file.getAbsolutePath());
			return file.getAbsolutePath();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static String exportCallOrder(Page<CardOrder> page) {
		List<CardOrder> cardOrders = page.getResult();

		String inputFileName = CookieRedis.get("exportPath");
		SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
		String now = dateFormat.format(new Date());
		//导出文件路径
		String basePath = inputFileName;

		//文件名
		String exportFileName =  cardOrders.size() > 0 ? cardOrders.get(0).getDownName() + "权益卡_"+now+".xlsx" : "权益卡_"+now+".xlsx";

		String[] cellTitle = {"产品名称","卡券号","密码","过期时间"};
		// 声明一个工作薄
		try {
			XSSFWorkbook workBook = new XSSFWorkbook();
			SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workBook, 1000);
			// 生成一个表格
			// 生成一个表格
			Sheet sheet = sxssfWorkbook.createSheet();
			workBook.setSheetName(0,cardOrders.size() > 0 ? cardOrders.get(0).getDownName() : "权益");
			// 创建表格标题行 第一行
			Row titleRow = sheet.createRow(0);
			for(int i=0;i<cellTitle.length;i++){
				titleRow.createCell(i).setCellValue(cellTitle[i]);
			}
			//插入需导出的数据
			for(int i= 0;i < cardOrders.size();i++){
				Row row = sheet.createRow(i+1);
				CardOrder slow = cardOrders.get(i);
				row.createCell(0).setCellValue(slow.getName());
				row.createCell(1).setCellValue(slow.getCardNum().toString());
				row.createCell(2).setCellValue(slow.getPasswd());
				row.createCell(3).setCellValue(DateUtils.toLongDate(slow.getExpireTime()));
			}
			File  file = new File(basePath+exportFileName);
			//文件输出流
			FileOutputStream outStream;
			try {
				outStream = new FileOutputStream(file);
				sxssfWorkbook.write(outStream);
				outStream.flush();
				outStream.close();
				System.out.println("导出2007文件成功！文件导出路径：--"+basePath+exportFileName);
				return basePath+exportFileName;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}
